package l2p.accountmanager;

import java.io.IOException;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.ResultSet;
import java.sql.SQLException;

import l2p.Base64;
import l2p.Config;
import l2p.Server;
import l2p.database.DatabaseUtils;
import l2p.database.FiltredPreparedStatement;
import l2p.database.L2DatabaseFactory;
import l2p.database.ThreadConnection;

public class SQLAccountManager
{
	private static String _uname = "";
	private static String _pass = "";
	private static String _level = "";
	private static String _comments = "";
	private static String _mode = "";

	public static void main(String[] args) throws SQLException, IOException, NoSuchAlgorithmException
	{
		Server.SERVER_MODE = Server.MODE_LOGINSERVER;
		Config.load();
		System.out.println("Please choose an option:");
		System.out.println("");
		System.out.println("1 - Create new account or update existing one (change pass and access level).");
		System.out.println("2 - Change access level.");
		System.out.println("3 - Delete existing account.");
		System.out.println("4 - List accounts & access levels.");
		System.out.println("5 - Exit.");
		LineNumberReader _in = new LineNumberReader(new InputStreamReader(System.in));
		while(!(_mode.equals("1") || _mode.equals("2") || _mode.equals("3") || _mode.equals("4") || _mode.equals("5")))
		{
			System.out.print("Your choice: ");
			_mode = _in.readLine();
		}

		if(_mode.equals("1") || _mode.equals("2") || _mode.equals("3"))
		{
			if(_mode.equals("1") || _mode.equals("2") || _mode.equals("3"))
				while(_uname.length() == 0)
				{
					System.out.print("username: ");
					_uname = _in.readLine();
				}

			if(_mode.equals("1"))
				while(_pass.length() == 0)
				{
					System.out.print("password: ");
					_pass = _in.readLine();
				}

			if(_mode.equals("1") || _mode.equals("2"))
				while(_level.length() == 0)
				{
					System.out.print("access level: ");
					_level = _in.readLine();
				}

			if(_mode.equals("1") || _mode.equals("2"))
				while(_comments.length() == 0)
				{
					System.out.print("comments: ");
					_comments = _in.readLine();
				}

		}

		if(_mode.equals("1"))
			// Add or Update
			AddOrUpdateAccount(_uname, _pass, _level, _comments);
		else if(_mode.equals("2"))
			// Change Level
			ChangeAccountLevel(_uname, _level);
		else if(_mode.equals("3"))
		{
			// Delete
			System.out.print("Do you really want to delete this account ? Y/N : ");
			String yesno = _in.readLine();
			if(yesno.equals("Y"))
				// Yes
				DeleteAccount(_uname);

		}
		else if(_mode.equals("4"))
			// List
			printAccInfo();

		System.out.println("Brought to you by L2 Phoenix dev team ;)");
		System.out.println("Have fun playing lineage2");
	}

	private static void printAccInfo() throws SQLException
	{
		int count = 0;
		ThreadConnection con = null;
		FiltredPreparedStatement statement = null;
		ResultSet rset = null;
		try
		{
			con = L2DatabaseFactory.getInstance().getConnection();
			statement = con.prepareStatement("SELECT login, access_level FROM accounts ORDER BY login ASC");
			rset = statement.executeQuery();
			while(rset.next())
			{
				System.out.println(rset.getString("login") + " -> " + rset.getInt("access_level"));
				count++;
			}
		}
		finally
		{
			DatabaseUtils.closeDatabaseCSR(con, statement, rset);
		}
		System.out.println("Number of accounts: " + count);
	}

	private static void AddOrUpdateAccount(String account, String password, String level, String comments) throws IOException, SQLException, NoSuchAlgorithmException
	{
		// Encode Password
		MessageDigest md = MessageDigest.getInstance("SHA");
		byte[] newpass;
		newpass = password.getBytes("UTF-8");
		newpass = md.digest(newpass);
		if(Integer.parseInt(level) > 100)
			level = "100";

		// Add to Base
		ThreadConnection con = null;
		FiltredPreparedStatement statement = null;
		try
		{
			con = L2DatabaseFactory.getInstance().getConnection();
			statement = con.prepareStatement("REPLACE	accounts (login, password, access_level, comments) VALUES (?,?,?,?)");
			statement.setString(1, account);
			statement.setString(2, Base64.encodeBytes(newpass));
			statement.setString(3, level);
			statement.setString(4, comments);
			statement.executeUpdate();
		}
		finally
		{
			DatabaseUtils.closeDatabaseCS(con, statement);
		}
	}

	private static void ChangeAccountLevel(String account, String level) throws SQLException
	{
		if(Integer.parseInt(level) > 100)
			level = "100";
		ThreadConnection con = null;
		FiltredPreparedStatement statement = null;
		ResultSet rset = null;
		try
		{
			con = L2DatabaseFactory.getInstance().getConnection();

			// Check Account Exist
			statement = con.prepareStatement("SELECT COUNT(*) FROM accounts WHERE login=?;");
			statement.setString(1, account);
			rset = statement.executeQuery();
			if(rset.next() && rset.getInt(1) > 0)
			{
				// Exist

				// Update
				statement = con.prepareStatement("UPDATE accounts SET access_level=? WHERE login=?;");
				statement.setEscapeProcessing(true);
				statement.setString(1, level);
				statement.setString(2, account);
				statement.executeUpdate();

				System.out.println("Account \"" + account + "\" Updated\n");
			}
			else
				// Not Exist
				System.out.println("Account \"" + account + "\" Not Exist\n");
		}
		finally
		{
			DatabaseUtils.closeDatabaseCSR(con, statement, rset);
		}
	}

	private static void DeleteAccount(String account) throws SQLException
	{
		ThreadConnection con = null;
		FiltredPreparedStatement statement = null;
		FiltredPreparedStatement subStatement = null;
		ResultSet rset = null, subRset = null;
		try
		{
			con = L2DatabaseFactory.getInstance().getConnection();
			// Check Account Exist
			statement = con.prepareStatement("SELECT COUNT(*) FROM accounts WHERE login=?;");
			statement.setString(1, account);
			rset = statement.executeQuery();
			if(rset.getInt(1) == 0)
			{
				System.out.println("Account \"" + account + "\" Not Exist\n");
				return;
			}
			// Account exist
			DatabaseUtils.closeDatabaseSR(statement, rset);

			// Get Accounts ID
			statement = con.prepareStatement("SELECT obj_Id, char_name, clanid FROM characters WHERE account_name=?;");

			statement.setEscapeProcessing(true);
			statement.setString(1, account);
			rset = statement.executeQuery();
			while(rset.next())
			{
				System.out.println("Deleting character \"" + rset.getString("char_name") + "\"\n");
				// Check If clan leader Remove Clan and remove all from it
				subStatement = con.prepareStatement("SELECT COUNT(*) FROM clan_data WHERE leader_id=?;");
				subStatement.setString(1, rset.getString("clanid"));
				subRset = subStatement.executeQuery();
				boolean isClanLeader = false;
				if(subRset.next() && subRset.getInt(1) > 0)
					isClanLeader = true;

				DatabaseUtils.closeDatabaseSR(subStatement, subRset);
				if(isClanLeader)
				{
					// Clan Leader

					// Get Clan Name
					subStatement = con.prepareStatement("SELECT clan_name FROM clan_data WHERE leader_id=?;");
					subStatement.setString(1, rset.getString("clanid"));
					subRset = subStatement.executeQuery();

					String clanName = null;
					if(subRset.next())
						clanName = subRset.getString("clan_name");

					System.out.println("Deleting clan \"" + clanName + "\"\n");

					DatabaseUtils.closeDatabaseSR(subStatement, subRset);

					// Delete Clan Wars
					subStatement = con.prepareStatement("DELETE FROM clan_wars WHERE clan1=? OR clan2=?;");
					subStatement.setEscapeProcessing(true);
					subStatement.setString(1, clanName);
					subStatement.setString(2, clanName);
					subStatement.executeUpdate();
					DatabaseUtils.closeStatement(subStatement);

					// Remove All From clan
					subStatement = con.prepareStatement("UPDATE characters SET clanid=0 WHERE clanid=?;");
					subStatement.setString(1, rset.getString("clanid"));
					subStatement.executeUpdate();
					DatabaseUtils.closeStatement(subStatement);

					// Delete Clan
					subStatement = con.prepareStatement("DELETE FROM clan_data WHERE clan_id=?;");
					subStatement.setString(1, rset.getString("clanid"));
					subStatement.executeUpdate();
					DatabaseUtils.closeStatement(subStatement);
				}

				// skills
				subStatement = con.prepareStatement("DELETE FROM character_skills WHERE char_obj_id=?;");
				subStatement.setString(1, rset.getString("obj_Id"));
				subStatement.executeUpdate();
				DatabaseUtils.closeStatement(subStatement);

				// shortcuts
				subStatement = con.prepareStatement("DELETE FROM character_shortcuts WHERE char_obj_id=?;");
				subStatement.setString(1, rset.getString("obj_Id"));
				subStatement.executeUpdate();
				DatabaseUtils.closeStatement(subStatement);

				// items
				subStatement = con.prepareStatement("DELETE FROM items WHERE owner_id=?;");
				subStatement.setString(1, rset.getString("obj_Id"));
				subStatement.executeUpdate();
				DatabaseUtils.closeStatement(subStatement);

				// recipebook
				subStatement = con.prepareStatement("DELETE FROM character_recipebook WHERE char_id=?;");
				subStatement.setString(1, rset.getString("obj_Id"));
				subStatement.executeUpdate();
				DatabaseUtils.closeStatement(subStatement);

				// quests
				subStatement = con.prepareStatement("DELETE FROM character_quests WHERE char_id=?;");
				subStatement.setString(1, rset.getString("obj_Id"));
				subStatement.executeUpdate();
				DatabaseUtils.closeStatement(subStatement);

				// macroses
				subStatement = con.prepareStatement("DELETE FROM character_macroses WHERE char_obj_id=?;");
				subStatement.setString(1, rset.getString("obj_Id"));
				subStatement.executeUpdate();
				DatabaseUtils.closeStatement(subStatement);

				// friends
				subStatement = con.prepareStatement("DELETE FROM character_friends WHERE char_id=? or friend_id = ?;");
				subStatement.setString(1, rset.getString("obj_Id"));
				subStatement.setString(2, rset.getString("obj_Id"));
				subStatement.executeUpdate();
				DatabaseUtils.closeStatement(subStatement);

				// boxaccess
				subStatement = con.prepareStatement("DELETE FROM boxaccess WHERE charname=?;");
				subStatement.setString(1, rset.getString("char_name"));
				subStatement.executeUpdate();
				DatabaseUtils.closeStatement(subStatement);

				// characters
				subStatement = con.prepareStatement("DELETE FROM characters WHERE obj_Id=?;");
				subStatement.setString(1, rset.getString("obj_Id"));
				subStatement.executeUpdate();
				DatabaseUtils.closeStatement(subStatement);
			}
			DatabaseUtils.closeDatabaseSR(statement, rset);

			// Delete Account
			statement = con.prepareStatement("DELETE FROM accounts WHERE login=?;");
			statement.setEscapeProcessing(true);
			statement.setString(1, account);
			statement.executeUpdate();
			System.out.println("Account \"" + account + "\" Deleted\n\n");

		}
		finally
		{
			DatabaseUtils.closeDatabaseSR(subStatement, subRset);
			DatabaseUtils.closeDatabaseCSR(con, statement, rset);
		}
	}

}
